Cannot drop database because it is currently in use in MS SQL Server

Fix/Solution: Cannot drop database because it is currently in use in MS SQL Server

USE [MorganDB]
GO
/****** Object:  Database [MorganDB]    Script Date: 11/29/2013 13:29:16 ******/
DROP DATABASE [MorganDB]
GO

When you run above script, you will get an error message
‘Msg 3702, Level 16, State 4, Line 2
Cannot drop database “MorganDB” because it is currently in use.
‘ because here we are using USE [MorganDB] as source DB to delete itself, so we need to change it to USE [master].

Fix/Solution:

USE [master]
GO
/****** Object:  Database [MorganDB]    Script Date: 11/29/2013 13:29:16 ******/
DROP DATABASE [MorganDB]
GO

Perfect Fix/Solution:

After changing source database as master, the script should works successfully. But sometimes connection may be opened by any other user. So, in that case, we also need to close existing open connections.

USE [master]
GO
ALTER DATABASE [MorganDB] SET  SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
USE [master]
GO
/****** Object:  Database [MorganDB]    Script Date: 11/29/2013 13:40:36 ******/
DROP DATABASE [MorganDB]
GO

Fix/Solution in C#: Cannot drop database because it is currently in use in MS SQL Server

You can use the following C# code to close existing database connections and Drop or Delete Database in MS Sql Server.

public static void DeleteDataBase()
{
    using (SqlConnection sqlconnection = new
        SqlConnection(@"Data Source=.sqlexpress;Initial Catalog=master;Integrated Security=SSPI;"))
    {
        sqlconnection.Open();
        // if you used master db as Initial Catalog, there is no need to change database
        sqlconnection.ChangeDatabase("master");

        string rollbackCommand = @"ALTER DATABASE [MorganDB] SET  SINGLE_USER WITH ROLLBACK IMMEDIATE";

        SqlCommand deletecommand = new SqlCommand(rollbackCommand, sqlconnection);

        deletecommand.ExecuteNonQuery();

        string deleteCommand = @"DROP DATABASE [MorganDB]";

        deletecommand = new SqlCommand(deleteCommand, sqlconnection);

        deletecommand.ExecuteNonQuery();
    }
}

Fix/Solution in Sql Server Management Studio for the error ‘Cannot drop database because it is currently in use’ in MS SQL Server

If you try to dropping a database in Sql Server Management Studio UI when an user connected to the SQL Server Database you will receive the below mentioned error message.

 You can avoid this error by checking the option Close existing connections.